# This block of code will create a blank SPI dataset (only containing country info) that will be appended to when each indicator is added.
# There will be two indicators added for each dimension
# 1. An indicator with a score between 0-1 for each dimension
# 2. An indicator with the raw (unscored) values of the indicators
# The unit for this database will be country*year

span <- c(2004:2019)

spi_df_empty <- bind_rows(replicate(length(span), wbstats::wbcountries(), simplify = FALSE), .id='date') %>%
  mutate(date=as.numeric(date)+span[1]-1) %>%
  filter(region!="Aggregates") # take out the aggregates (LAC, SAR, etc)

spi_df <- spi_df_empty

#read list of iso3c codes for matching from UN (https://unstats.un.org/unsd/methodology/m49/)
iso3c <- read_csv(paste(raw_dir,'metadata/iso_codes.csv', sep="/"),
                  col_types=list(col_character(), col_character(), col_character()))

Introduction

In this program, I will clean and reshape the raw data files in the 01_rawdata folder in order to produce the Statistical Performance Indicators.

Below is an overview of the SPI framework.

# Handling missing data

It is likely that for the initial launch of the Dashboard there will be several types of missing data.

In the absence of any new data, the existing material collected for the Statistical Performance Index could be used to populate an initial dashboard as follows:

• Data use dimension: leave blank

• Data services dimension: use scores for SPI “dissemination practices and openness” dimension

• Data products dimension: use scores for SPI “availability of key indicators” dimension

• Data sources dimension: use scores for SPI “censuses and surveys” dimension

• Data infrastructure dimension: use scores for SPI “methodology, standard and classifications” dimension

Indeed, a first iteration Dashboard and toolkit can be demonstrated using this existing data. As new methods are developed and data are collected, the Dashboard can progressively be populated. Where it has not been possible to develop and implement a methodology for the dashboard tile concerned, this should be left blank to signify that this is an important issue that has not yet been quantified in a way that meets the criteria for the Dashboard. This will provide an incentive for a research agenda and possibly surveys and other mechanisms to fill the gap for a particular purpose eg a country assessment.

Where it has been possible to develop and implement a methodology but it has not been possible to find data for a specific country, even though it is known to exist, the Dashboard tile for that country should also be left blank and weighting between tiles conducted on the basis of the tiles that are present.

In some situations, missing data represents a zero score, for example if there is no evidence that geospatial data is used by the country’s statistical system. In such cases a score of zero should be recorded and used in the generation of indicators, weightings and rankings.

Data Use

Cleaning for Data Use Indicators. Data Use (5 Indicators):
- 1.1_DUNL - Indicator 1.1: Data use by national legislature
- 1.2_DUNE - Indicator 1.2: Data use by national executive branch
- 1.3_DUCS - Indicator 1.3: Data use by civil society
- 1.4_DUAC - Indicator 1.4: Data use by academia
- 1.5_DUIO - Indicator 1.5: Data use by international organizations

Indicator 1.1: Data use by national legislature

Based on PARIS21 data use indicator (Chapter 4 of Statistical Capacity Development Outlook) using national legislature website as source.

Indicator 1.2: Data use by national executive branch

Based on PARIS21 data use indicator (Chapter 4 of Statistical Capacity Development Outlook) using voluntary national review plans as a source.

Our main source for this is the UN Voluntary National Reviews (VNR) Database (https://sustainabledevelopment.un.org/vnrs/).

The indicator for each data use by the national executive branch is formed by:
1. Searches the documents for references to a set of keywords related to each SDG goal
2. Searches the documents for the subset of references that also include statistics, by searching for numeric values (excluding references to years)
3. Calculates the ratio between the two

The ratio captures density of discussion of each topic in each document assessed. The keywords are based on indicators found in the World Bank’s World Development Indicators (WDI).

#load data
vnr_use_df <- read_csv(paste(raw_dir, "/1.2_DUNE/UN_VNR_use_of_numbers.csv", sep="")) %>%
  group_by(country) %>%
  filter(year==max(year, na.rm=T)) %>%
  mutate(date=2019)


vnr_use_merge <- vnr_use_df %>% 
  group_by(country, date, name) %>%
  summarise(SPI.D1.2.VNR=wtd.mean(freq_of_numbers, w=freq_of_terms))


#add to spi databases
spi_df <- spi_df %>%
  left_join(vnr_use_merge)

#map the VNR data

#overall score
spi_mapper('vnr_use_merge', 'SPI.D1.2.VNR', 'Overall Score for Use of Numbers in VNRs')

# #SDG 1
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 1") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 1')
# 
# #SDG 2
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 2") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 2')
# 
# #SDG 3
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 3") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 3')
# 
# #SDG 4
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 4") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 4')
# 
# #SDG 5
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 5") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 5')
# 
# #SDG 6
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 6") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 6')
# 
# #SDG 7
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 7") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 7')
# 
# #SDG 8
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 8") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 8')
# 
# #SDG 9
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 9") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 9')
# 
# #SDG 10
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 10") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 10')
# 
# #SDG 11
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 11") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 11')
# 
# #SDG 12
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 12") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 12')
# 
# #SDG 13
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 13") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 13')
# 
# #SDG 14
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 14") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 14')
# 
# #SDG 15
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 15") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 15')
# 
# #SDG 16
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 16") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 16')
# 
# #SDG 17
# vnr_map <- vnr_use_df %>%
#   filter(SDG=="SDG 17") 
# 
# spi_mapper('vnr_map', 'freq_of_numbers', 'Use of Numbers in VNRs for SDG 17')

Indicator 1.3: Data use by civil society

Based on PARIS21 data use indicator (Chapter 4 of Statistical Capacity Development Outlook) using main social media platform in use in country as source.

We are pulling data from Twitter at this point. We have only accessed a handful of accounts from NSOs, but this could be quickly scaled up.

Indicator 1.4: Data use by academia

The idea for this indicator is that countries should be producing statistical products that are utilized, in this case by academia. As a measure of this, we use the IHSN database on censuses and surveys conducted by counties and calculate a ranking of countries based on the total number of downloads of the censuses and surveys produced by that country on a per capita basis. In other words, we calculate the total number of downloads of censuses and surveys for a country, divided by the population of that country, to produce a ranking. Countries with low scores on this metric, may either be producing censuses and surveys that are not in high demand or are not producing or adding a sufficient number of censuses and surveys to IHSN to register highly on this measure.

The source for this indicator is the IHSN.

The mission of the IHSN is to improve the availability, accessibility, and quality of survey data within developing countries, and to encourage the analysis and use of this data by national and international development decision makers, the research community, and other stakeholders.

To support this mission, the key objectives of the IHSN are:

Improved coordination of internationally sponsored survey programs, with emphasis on timing, sequencing, frequency, and cost-effectiveness Availability of coordinated and practical technical and methodological guidelines for all stages of the survey life cycle Availability of a central survey data catalog which would inform data users of the availability of survey and census data from multiple sources Availability of standards, tools, and guidelines that would allow data producers to document, disseminate, and preserve microdata according to international standards and best practices Improved collaboration between data producers and users

IHSN has a database of more than 7,483 surveys as of August 5, 2020.

Indicator 1.5: Data use by international organizations

The following indicator will measure how useful data sources produced in national statistics systems are for international organizations. We gather three measures of how useful or reliable country produced measures are for international organizations. The first on comparability of poverty estimates for the World Bank reporting on international poverty. A second on statistics on child mortality for the UN Inter-agency Group for Child Mortality Estimation. A third on accuracy of debt reporting as classified by the World Bank. Each of these will be described in greater detail below.

While these three indicators are not comprehensive of all statistics produced by countries, they do provide some indication of whether countries are producing estimates that are useful to international organizations for their reporting and monitoring efforts.

Poverty Comparability

The first measure examines whether comparable poverty estimates are produced by countries over time. The World Bank’s Povcalnet has introduced a comparability indicator for whether the country produces comparable poverty estimates over time. https://blogs.worldbank.org/opendata/apples-apples-povcalnet-introduces-new-comparability-indicator. This metadata has been compiled by the Country Poverty Economists in the World Bank’s Poverty and Equity Global Practice. From the Povcalnet team:

As countries frequently improve household surveys and measurement methodologies, strict comparability of poverty estimates over time is often limited. Strictly comparable poverty estimates within a country require a consistent production process, including the sampling frame, questionnaires, the methodological construction of the welfare aggregates and poverty lines, a consistent deflation of prices in time and space, among many  other considerations. Thus, the assessment of comparability is country-dependent and relies on the knowledge of the Country Poverty Economist and Regional Poverty Teams of the Poverty Global Practice, as well as close  dialogue with national data producers with intimate knowledge of the survey design and methodology. Within a country, we assume comparability of poverty estimates over time unless there is a known change to survey     methodology, measurement or data structure. More details on the comparability metadata database can be found in Atamanov et al. (2019) (section 4). The database can be downloaded in csv.

The data will be pulled from the WDI and combined with metadata from the Povcalnet

Scoring is as follows: Quality (1 points total): 1 Point. Comparable data lasting at least two years within past 5 years 0 Points. No comparable data within past 5 years

library(povcalnetR) 

#the code below is based on public released code by povcalnet team:
#https://github.com/worldbank/Global_Poverty_Blogs/blob/master/bg_povcalnet_comparability/R/comparability_breaks.R

# some contants
year_range <- 1990:2020
metadata_path <- "https://development-data-hub-s3-public.s3.amazonaws.com/ddhfiles/506801/povcalnet_comparability.csv"

# Load data ---------------------------------------------------------------

metadata <- read_csv(metadata_path)
cov_lkup <- c(3, 2, 1, 4)
names(cov_lkup) <- c("N", "U", "R", "A")

dat_lkup <- c(2,1)
names(dat_lkup) <- c("income","consumption")


pcn <- povcalnet()
pcn$coveragetype <- cov_lkup[pcn$coveragetype]
pcn$datatype <- dat_lkup[pcn$datatype]

df <- pcn %>%
  mutate(countrycode=as.character(countrycode)) %>%
  inner_join(metadata, by = c("countrycode", "year", "coveragetype", "datatype"))


#Now loop from 2016 and 2019, keeping just data inside last 5 years.
for (reference_year in 2004:2019) {
  
  temp<-df %>%
        filter(coveragetype %in% c(3,4)) %>% #keep just nationally representative samples
        mutate(frequency=((reference_year-as.numeric(year))<=5) & (reference_year>=as.numeric(year))) %>% 
        filter(frequency==TRUE) %>%
        group_by(countrycode, comparability) %>% #for each country and comparability type, get number of comparable estimates
        summarise(SPI.D1.5.POV=n())  %>%
        ungroup() %>%
        group_by(countrycode) %>% #now get a total by country with the max number of comparable estimates
        summarise(SPI.D1.5.POV=max(SPI.D1.5.POV, na.rm=T)) %>%
        mutate(SPI.D1.5.POV=if_else(SPI.D1.5.POV>=2,1,0)) %>% #only give point if there is at least two observations that are comparable
        mutate(date=reference_year,
                 iso3c=countrycode) %>%
        left_join(country_metadata) %>%
        select( country,iso3c, date, starts_with('SPI.D1.5.POV')) 



  assign(paste("D3.1.AKI",reference_year,sep="_"), temp)
}

#now append together and save
for (i in c(2004:2019)) {
  
  temp<-get(paste('D3.1.AKI_',i, sep=""))
  
  if (!exists('D3.1.AKI')) {
    D3.1.AKI<-temp
  } else {
    D3.1.AKI<-D3.1.AKI %>%
      bind_rows(temp) %>%
      arrange(-date, iso3c)
  }
}

Mortality rate, under-5 (per 1,000 live births)

UN IGME makes all available source data on child mortality rates available with metadata on quality.

https://childmortality.org/data/

Source data can bedivided into data included in the estimation model and data excluded from the estimation model (note: data may be excluded for any one of several reasons including data quality or by rule—for example, indirect estimates from summary birth histories are not included when direct estimates from the full birth history from the same survey or census are available).

Countries are assessed for whether they can produce indicators over a five year period that meet the thresholds set by UN IGME.

Quality (1 points total): 1 Point. At least three indicators that met UN IGME standards within past 5 years
0.67 Points. Two indicators that met UN IGME standards within past 5 years 0.33 Points. One indicators that met UN IGME standards within past 5 years 0 Points. None within past 5 years

Data was pulled on April 13, 2020.

#Read in data from UN Inter-agency Group for Child Mortality Estimation



D3.3.AKI.MORT <- read_csv(file=paste(raw_dir, '3.1_DPSS/D3.3_child_mort_RAW_v01.csv', sep="/")) %>%
  filter(INDICATOR=='Under-five mortality rate' & SEX=='Total') %>% #keep just observations for under 5 child mortality and for both sexes
  filter(OBS_STATUS=='Included in IGME') %>% # Also keep only surveys that met IGME criteria for inclusion as a nationally representative statistic
  mutate(date=as.numeric(str_extract(TIME_PERIOD, "^.{4}")),
         country=REF_AREA,
         D3.CHLD.MORT=OBS_VALUE)

#Now loop from 2016 and 2019, keeping just data inside last 5 years.
for (reference_year in 2004:2019) {
  temp <-D3.3.AKI.MORT %>%
          mutate(frequency=((reference_year-as.numeric(date))<=5) & (reference_year>=as.numeric(date))) %>%
          mutate(SPI.FREQ.D1.5.CHLD.MORT=if_else(frequency==TRUE,1,0)) %>% #create 0,1 variable for whether data point exists for country
          group_by(country) %>%
          summarise(SPI.FREQ.D1.5.CHLD.MORT=sum(SPI.FREQ.D1.5.CHLD.MORT, na.rm=T)) %>%
          mutate(SPI.FREQ.D1.5.CHLD.MORT=case_when(
            SPI.FREQ.D1.5.CHLD.MORT>=3 ~ 1,
            SPI.FREQ.D1.5.CHLD.MORT==2 ~ 0.67,
            SPI.FREQ.D1.5.CHLD.MORT==1 ~ 0.33,
            SPI.FREQ.D1.5.CHLD.MORT==0 ~ 0,
            TRUE ~ 0
          )) %>%
          mutate(SPI.D1.5.CHLD.MORT=SPI.FREQ.D1.5.CHLD.MORT) %>%
          mutate(date=reference_year) %>%
          select( country, date, starts_with('SPI.')) %>%
          select(-SPI.FREQ.D1.5.CHLD.MORT)


  assign(paste("D3.3.AKI",reference_year,sep="_"), temp)
}

#now append together and save
for (i in c(2004:2019)) {
  
  temp<-get(paste('D3.3.AKI_',i, sep=""))
  
  if (!exists('D3.3.AKI')) {
    D3.3.AKI<-temp
  } else {
    D3.3.AKI<-D3.3.AKI %>%
      bind_rows(temp) %>%
      arrange(-date, country)
  }
}

Debt service (PPG and IMF only, % of exports of goods, services and primary income)

For this indicator, Debt service (PPG and IMF only, % of exports of goods, services and primary income), we will pull data from the WDI but modify the scoring using the WDI metadata on whether the external debt data is actual, estimated, or preliminary. The status “as reported (actual)” indicates that the country was fully current in its reporting under the DRS and that World Bank staff are satisfied that the reported data give an adequate and fair representation of the country’s total public debt. “Preliminary” data are based on reported or collected information, but because of incompleteness or other reasons, an element of staff estimation is included. “Estimated” data indicate that countries are not current in their reporting and that a significant element of staff estimation has been necessary for producing the data tables.

Scoring is as follows:

Quality:

1 Points. Actual value 0.67 Points. Preliminary value 0.33 Points. Estimated value 0 Points. No value

# pull in some WDI metadata which will be used for constructing AKI indicators.
# WDI metadata was gathered using previous vintages of WDI from 2016-19
# Metadata was gathered and saved as csv files in the 011_data folder

for (i in 2016:2019) {
  temp <- read_csv(file = paste(raw_dir, "/metadata/WDI_metadata_",i,".csv", sep="" )) %>%
    as_tibble(.name_repair='universal') %>%
    mutate(National.accounts.reference.year=as.numeric(National.accounts.reference.year),
           date=i)
  
    assign(paste("WDI_metadata",i,sep="_"), temp)
}

WDI_metadata <- bind_rows(WDI_metadata_2016, WDI_metadata_2017, WDI_metadata_2018, WDI_metadata_2019)

#reshape metaadata file
D3.15.AKI <- WDI_metadata %>%
  transmute(iso3c=if_else(is.na(Country.Code), Code, Country.Code),
            date=date,
            External_debt_Reporting=External.debt.Reporting.status,
            Income.Group=Income.Group) %>%
  mutate(SPI.D1.5.DT.TDS.DPPF.XP.ZS= case_when(
            External_debt_Reporting=='Actual' ~ 1,
            External_debt_Reporting=='Preliminary' ~ 0.67,
            External_debt_Reporting=='Estimate' ~ 0.33,
            TRUE ~ 0
          )) %>% 
  mutate(SPI.D1.5.DT.TDS.DPPF.XP.ZS=if_else((Income.Group=="High income" & is.na(External_debt_Reporting)),1,SPI.D1.5.DT.TDS.DPPF.XP.ZS)) %>% #fix an issue where high income countries are not judged on their debt reporting
  ungroup() %>%
  select(iso3c, date,  contains('SPI.D1.5.DT.TDS.DPPF.XP.ZS'))

#now combine AKI databases and write to csv
D3.AKI <- spi_df_empty %>%
  left_join(D3.1.AKI) %>%
  left_join(D3.3.AKI) %>%
  left_join(D3.15.AKI) %>%
  right_join(spi_df_empty) %>%
  mutate_at(vars(starts_with('SPI.D1.5.')), ~if_else(is.na(.),0,as.numeric(.))) 


spi_df <- spi_df %>%
  left_join(D3.AKI)

aki <- c(
             SPI.D1.5.POV='Poverty headcount ratio at $1.90 a day (2011 PPP) (% of population)',
             SPI.D1.5.CHLD.MORT='Mortality rate, under-5 (per 1,000 live births)', 
             SPI.D1.5.DT.TDS.DPPF.XP.ZS='Debt service (PPG and IMF only, % of exports of goods, services and primary income)'
)


for (ind in names(aki)) {
  ind <- ind
  names(aki)
  print(ind)
  temp_map <- D3.AKI %>%
    filter(.data[[ind]] >= 0)
  
  spi_mapper('temp_map', ind, aki[[ind]])
}

## [1] "SPI.D1.5.POV"

## [1] "SPI.D1.5.CHLD.MORT"

## [1] "SPI.D1.5.DT.TDS.DPPF.XP.ZS"

Data Services

Cleaning for Data Services Indicators. Data Services (4 Indicators):
- 2.1_DSDR - Indicator 2.1: Data releases
- 2.2_DSOA - Indicator 2.2: Online access
- 2.3_DSAS - Indicator 2.3: Advisory/ Analytical Services
- 2.4_DSDS - Indicator 2.4: Data services

Indicator 2.1: Data Releases

Data Dissemination Standard (SDDS) and electronic General Data Dissemination Standard (e-GDDS) were established by the International Monetary Fund (IMF) for member countries that have or that might seek access to international capital markets, to guide them in providing their economic and financial data to the public. Although subscription is voluntary, the subscribing member needs to be committed to observing the standard and provide information about its data and data dissemination practices (metadata). The metadata are posted on the IMF’s SDDS and e-GDDS websites.

1 Point. Subscribing to IMF SDDS+ or SDDS standards 0.5 Points. Subscribing to IMF e-GDDS standards 0 Points. Otherwise

Request_metadata <- GET(url = "http://api.worldbank.org/v2/country/all/indicator/5.21.01.01.sdds?format=json&date=2004:2015&per_page=5000")
Response_metadata <- content(Request_metadata, as = "text", encoding = "UTF-8")

# Parse the JSON content and convert it to a data frame.
D2.1_DSDR_sci <- jsonlite::fromJSON(Response_metadata, flatten = TRUE) %>%
  data.frame() %>%
  transmute(
    iso3c=countryiso3code,
    country=country.value,
    date=as.numeric(date),
    SDDS=value
  ) %>%
  left_join(spi_df_empty) %>% #add on country metadata
  filter(!is.na(income)) %>%
  select(iso3c, country, date, SDDS  ) 



#read in metadata file.

#pull data for several of the standards from WDI metadata

df <- WDI_metadata

# Manipulate and clean final data
df <- df %>%
  filter(!is.na(Income.Group))  #keep just countries (drop aggregations)


D2.1_DSDR <- df %>%
  mutate(iso3c=if_else(is.na(Country.Code), Code, Country.Code),
         country=Table.Name) %>%
  select(c('iso3c', 'country', 'date',  'IMF.data.dissemination.standard')  ) %>%
  mutate(IDDS=IMF.data.dissemination.standard) %>%
  mutate(SPI.D2.1.GDDS=case_when(
    IDDS=="Special Data Dissemination Standard Plus (SDDS+)" | IDDS=="Special Data Dissemination Standard (SDDS)"~ 1, 
    IDDS=="Enhanced General Data Dissemination System (e-GDDS)"~ 0.5,
    TRUE ~ 0 ),
    SDDS=case_when(
     IDDS=="Special Data Dissemination Standard (SDDS)"~ 1, 
    TRUE ~ 0 ))  %>%
  bind_rows(D2.1_DSDR_sci) %>%
  rename(RAW.D2.1.IDDS=IDDS) %>%
  select(iso3c, country, date, RAW.D2.1.IDDS, SPI.D2.1.GDDS  ) %>%
  arrange(date, country) 

D2.1_DSDR_map <- D2.1_DSDR %>%
  filter(RAW.D2.1.IDDS>0)

spi_mapper('D2.1_DSDR_map', 'SPI.D2.1.GDDS', 'SDDS Data Standards in Place' )

#add to spi databases
spi_df <- spi_df %>%
  left_join(D2.1_DSDR)

Indicator 2.2: Online access

This indicator measures the richness and openness of online access.

Source

Our source for this indicator is Open Data Watch. From Open Data watch:

The Open Data Inventory (ODIN) assesses the coverage and openness of official statistics to help identify gaps, promote open data policies, improve access, and encourage dialogue between national statistical offices (NSOs) and data users. ODIN 2018/19 includes 178 countries, including most all OECD countries. Two-year comparisons are for all countries with two years of data between 2015-2017. Scores can be compared across topics and countries.

We use the Openness score from ODIN for this measure. The score ranges from 0-100. It contains scores along five dimensions:
- Machine Readability
- Non-Proprietary format
- Download Options
- Metadata Available
- Terms of Use

A description for each of these five dimensions is below:

Machine Readability

Openness element 1 measures whether data are available in a machine readable format such as XLS, XLSX, CSV, and JSON. Machine-readable file formats allow users to easily process data using a computer. When data are made available in formats that are not machine readable, users cannot easily access and modify the data, which severely restricts the scope of the data’s use. In many cases PDF versions of datasets within reports can be useful to users, as the text in conjunction with the tables gives context and explanation to the figures which helps less technical users understand the data. Because of this, ODIN assessments do not penalize countries for making datasets available in PDF or other non-machine readable formats, unless these formats are the only option for exporting data. Scores are not penalized for having identical datasets in both machine readable and non-readable formats. Compression formats do not affect machine readability scores, only non-proprietary scores (see next page). Scores are given by data category, not indicator.

Non-Proprietary format

For the elements of data openness, scoring is calculated independent of the data coverage. If data files are compressed in RAR format (which is proprietary), data for that indicator should be considered proprietary even if the enclosing files are in a non-proprietary format. Files compressed in ZIP format are not affected.

Download Options

Openness element 3 measures whether data are available with three different download options: bulk download, API, and user-select options. A bulk download is defined at the indicator level as: The ability to download all data recorded in ODIN for a particular indicator (all years, disaggregations, and subnational data) in one file, or multiple files that can be downloaded simultaneously. Bulk downloads are a key component of the Open Definition, which requires data to be “provided as a whole . . . and downloadable via the internet.” User-selectable download options are defined as: Users must be able to select an indicator and at least one other dimension to create a download or table. These dimensions could include time periods, geographic disaggregations, or other recommended disaggregations. An option to choose the file export format is not enough. API stands for Application Programming Interface. Ideally, APIs should be clearly displayed on the website. ODIN assumes APIs are available for the NSOs entire data collection used in ODIN, unless clearly stated. ODIN assessors do not register for use or test API functionality. For more information on APIs, see this guide. Scores are given by data category, not indicator.

Metadata Available

Openness element 4 measures whether metadata are made available. Scores are given by data category, not indicator. Metadata are defined at the indicator level as information about how the data are defined/calculated and collected. ODIN classifies metadata into three categories: (1) Not Available, (2) Incomplete, and (3) Complete. The following must be available to classify metadata as complete: • Definition of the indicator, or definition of key terms used in the indicator description (as applicable), or how the indicator was calculated. • Publication (date of upload), compilation date (date on front of report is not sufficient), or date dataset was last updated. • Name of data source (what agency collected the data). If the metadata only have one or two of the above elements, they are scored as incomplete

Terms of Use

Openness element 5 measures whether data are available with an open terms of use. Generally, terms of use (TOU) will apply to an entire website or data portal (unless otherwise specified). In these cases, all data found on the same website and/or portal will receive the same score. If a portal is located on the same domain as the NSO website, the terms of use on the NSO site will apply. If the data are located on a portal or website on a different domain, another terms of use will need to be present. For a policy/ license to be accepted as a terms of use, it must clearly refer to the data found on the website. Terms of use that refer to nondata content (such as pictures, logos, etc.) of the website are not considered. A copyright symbol at the bottom of the page is not sufficient. A sentence indicating a recommended citation format is not sufficient. Terms of use are classified the following ways: (1) Not Available, (2) Restrictive, (3) Semi-Restrictive, and (4) Open. If the TOU contains one or more restrictive clauses, it receives 0 points and is classified as “restrictive.” Restrictive clauses include:

For more details, consult the ODIN technical documentation: https://docs.google.com/document/d/1ubPL1l_3im9bjlCVZ6W2ICAy6UAiXl1hGeA1aXImkxI/edit#

  #read in ODIN data
  for (i in 2015:2018) {
   temp <- read_csv(paste(raw_dir, '/2.2_DSOA/','ODIN_',i,'.csv', sep=""))  %>%
        as_tibble(.name_repair = 'universal') %>%
        mutate(date=i) %>%
        filter(Data.categories=='All Categories')

    assign(paste('openness_df',i,sep="_"), temp)
  }

#bind different years together
openness_df <- bind_rows(openness_df_2015, openness_df_2016, openness_df_2017, openness_df_2018)


openness_df <- openness_df %>%
    select(Country.Code, date, Machine.readable, Non.proprietary, Download.options, Metadata.available, Terms.of.use, Openness.subscore) %>%
    rename(iso3c=Country.Code) %>%
    group_by( iso3c) %>%
    right_join(country_metadata) %>%
    mutate(
      across(c('Machine.readable', 'Non.proprietary', 'Download.options', 'Metadata.available', 'Terms.of.use', 'Openness.subscore'),~if_else(is.na(.), 0, .)           )
      
           )  

#do some quick renaming and formatting
openness_df_temp1 <- openness_df %>% 
  rename_with(~paste('RAW.D2.2',., sep="."), .cols=c('Machine.readable', 'Non.proprietary', 'Download.options', 'Metadata.available', 'Terms.of.use', 'Openness.subscore') ) 


openness_df_temp2 <- openness_df %>% 
  mutate(across(c('Machine.readable', 'Non.proprietary', 'Download.options', 'Metadata.available', 'Terms.of.use', 'Openness.subscore'), ~./100)) %>%
  rename_with(~paste('SPI.D2.2',., sep="."), .cols=c('Machine.readable', 'Non.proprietary', 'Download.options', 'Metadata.available', 'Terms.of.use', 'Openness.subscore') ) 

openness_df <- openness_df_temp1 %>%
  left_join(openness_df_temp2)

#add to spi dataframe
spi_df <- spi_df %>%
  left_join(openness_df)

Indicator 2.3: Advisory/ Analytical Services

No established source exists for this indicator. This is experimental.

Indicator 2.4: Data services

NSO has a listing of surveys and microdata sets that can provide the necessary data and reference for follow-up. Upon well-defined request and procedure per the national law and practice, users and practitioners can obtain the data collected from the households and businesses when needed.

NADA is an open source microdata cataloging system, compliant with the Data Documentation Initiative (DDI) and Dublin Core’s RDF metadata standards. It serves as a portal for researchers to browse, search, compare, apply for access, and download relevant census or survey datasets, questionnaires, reports and other information.

1 Point. Yes 0 Points. No

#read in csv file.
D2.4_NADA <- read_csv(file = paste(raw_dir, '/2.4_DSDS/', "D2.4.NADA.csv", sep="/" )) %>%
  mutate(SPI.D2.4.NADA=case_when(
    NADA==1 ~ 1, 
    NADA==0 ~ 0, 
    TRUE ~ 0 
  ))  %>%
  rename(RAW.D2.4.NADA=NADA,
         RAW.D2.4.NADA_text=NADA_text) %>%
  select(iso3c, country, date, RAW.D2.4.NADA,RAW.D2.4.NADA_text, SPI.D2.4.NADA  ) %>%
  arrange(date, country)

D2.4_NADA_map <- D2.4_NADA %>%
  filter(SPI.D2.4.NADA>0)

spi_mapper('D2.4_NADA_map', 'RAW.D2.4.NADA', 'NADA in Place' )

#add to spi databases
spi_df <- spi_df %>%
  left_join(D2.4_NADA)

Data Products

Cleaning for Data Products Indicators. Data Products (16 Indicators):

GOAL 1: No Poverty
GOAL 2: Zero Hunger
GOAL 3: Good Health and Well-beingq
GOAL 4: Quality Education
GOAL 5: Gender Equality
GOAL 6: Clean Water and Sanitation
GOAL 7: Affordable and Clean Energy
GOAL 8: Decent Work and Economic Growth
GOAL 9: Industry, Innovation and Infrastructure
GOAL 10: Reduced Inequality
GOAL 11: Sustainable Cities and Communities
GOAL 12: Responsible Consumption and Production
GOAL 13: Climate Action
GOAL 14: Life Below Water is excluded because this indicator is not relevant for landlocked countries
GOAL 15: Life on Land
GOAL 16: Peace and Justice Strong Institutions
GOAL 17: Partnerships to achieve the Goal

Scoring Approach

Download the latest SDG indicator data from UN Stats (https://unstats.un.org/sdgs/indicators/en/#) using their API
Transform the data so that for each indicator we can create a score documenting whether a value exists for the country in a year, whether the value is based on country data, country data adjusted, estimated, or modelled data according the UN Stats metadata. This will only include tier 1 indicators.
Combine the resulting data into a single set of indicators for use in the Statistical Performance Indicators dashboard and index by calculating the average across SDGs. This results in 17 total indicators. One for each SDG, which is the share of indicators with a value meeting quality criteria defined below.

Below is a paraphrased description from the UN stats webpage (https://unstats.un.org/sdgs/indicators/indicators-list/):

The global indicator framework for Sustainable Development Goals was developed by the Inter-Agency and Expert Group on SDG Indicators (IAEG-SDGs) and agreed upon at the 48th session of the United Nations Statistical Commission held in March 2017.

The global indicator framework includes 231 unique indicators. Please note that the total number of indicators listed in the global indicator framework of SDG indicators is 247. However, twelve indicators repeat under two or three different targets.

For each value of the indicator, the responsible international agency has been requested to indicate whether the national data were adjusted, estimated, modelled or are the result of global monitoring. The “nature” of the data in the SDG database is determined as follows:

Country data (C): Produced and disseminated by the country (including data adjusted by the country to meet international standards);
Country data adjusted (CA): Produced and provided by the country, but adjusted by the international agency for international comparability to comply with internationally agreed standards, definitions and classifications;
Estimated (E): Estimated based on national data, such as surveys or administrative records, or other sources but on the same variable being estimated, produced by the international agency when country data for some year(s) is not available, when multiple sources exist, or when there are data quality issues;
Modelled (M): Modelled by the agency on the basis of other covariates when there is a complete lack of data on the variable being estimated;
Global monitoring data (G): Produced on a regular basis by the designated agency for global monitoring, based on country data. There is no corresponding figure at the country level.

Scoring

For each indicator, we will produce a value for each country with the following coding scheme:

1 Point: Indicator exists and the value is based on the country, country data adjusted, or estimated or Global Monitoring data
0 Points: Indicator based on modeled data or does not exists

We give countries no credit for modeled data, because the country did not produce indicators in a form that was directly usable for reporting on an SDG indicator.

When we average over all indicators in a goal to get a score, we compute a 5 year moving average to avoid year to year variability in reporting for SDGs. The overall score for an SDG is then the 5 year average of the percentage of indicator values based on country, country data adjusted, or estimated or Global Monitoring data that were available for the SDG.

Because of the large data files and runtime of the program to calculate these indicators, the code to produce these indicators is in a separate file. https://github.com/stacybri/UN_Stats_SDG_Indicators_SPI/blob/master/02_programs/un_stats_cleaning.Rmd

un_sdg_df <- read_csv(paste(raw_dir,'/3_DP/SPI_D3_UNSD_data_',window,'yr.csv',sep="")) %>%
  rename(SPI.D3.1.POV   = SPI.D3.1,
         SPI.D3.2.HNGR  = SPI.D3.2,
         SPI.D3.3.HLTH  = SPI.D3.3,
         SPI.D3.4.EDUC  = SPI.D3.4,
         SPI.D3.5.GEND  = SPI.D3.5,
         SPI.D3.6.WTRS  = SPI.D3.6,
         SPI.D3.7.ENRG  = SPI.D3.7,
         SPI.D3.8.WORK  = SPI.D3.8,
         SPI.D3.9.INDY  = SPI.D3.9,
         SPI.D3.10.NEQL = SPI.D3.10,
         SPI.D3.11.CITY = SPI.D3.11,
         SPI.D3.12.CNSP = SPI.D3.12,
         SPI.D3.13.CLMT = SPI.D3.13,
         SPI.D3.14.LFWT = SPI.D3.14,
         SPI.D3.15.LAND = SPI.D3.15,
         SPI.D3.16.INST = SPI.D3.16,
         SPI.D3.17.PTNS = SPI.D3.17
) %>%
  select(country, date, starts_with("SPI")) %>%
  select(-SPI.D3.14.LFWT)

#add to spi databases
spi_df <- spi_df %>%
  left_join(un_sdg_df)

#map the values
un_sdg_map <- un_sdg_df %>%
  select(country, date, starts_with("SPI")) 

spi_mapper('un_sdg_map','SPI.D3.1.POV','Goal 1: No Poverty')

spi_mapper('un_sdg_map','SPI.D3.2.HNGR','Goal 2: No Hunger')

spi_mapper('un_sdg_map','SPI.D3.3.HLTH','Goal 3: Good Health and Well-being')

spi_mapper('un_sdg_map','SPI.D3.4.EDUC','Goal 4: Quality Education  ')

spi_mapper('un_sdg_map','SPI.D3.5.GEND','Goal 5: Gender Equality ')

spi_mapper('un_sdg_map','SPI.D3.6.WTRS','Goal 6: Clean Water and Sanitation ')

spi_mapper('un_sdg_map','SPI.D3.7.ENRG','Goal 7: Affordable and Clean Energy')

spi_mapper('un_sdg_map','SPI.D3.8.WORK','Goal 8: Decent Work and Economic Growth  ')

spi_mapper('un_sdg_map','SPI.D3.9.INDY','Goal 9: Industry, Innovation and Infrastructure ')

spi_mapper('un_sdg_map','SPI.D3.10.NEQL','Goal 10: Reduced Inequality   ')

spi_mapper('un_sdg_map','SPI.D3.11.CITY','Goal 11: Sustainable Cities and Communities')

spi_mapper('un_sdg_map','SPI.D3.12.CNSP','Goal 12: Responsible Consumption and Production   ')

spi_mapper('un_sdg_map','SPI.D3.13.CLMT','Goal 13: Climate Action')

#spi_mapper('un_sdg_map','SPI.D3.14.LFWT','Goal 14: Life Below Water')
spi_mapper('un_sdg_map','SPI.D3.15.LAND','Goal 15: Life on Land')

spi_mapper('un_sdg_map','SPI.D3.16.INST','Goal 16: Peace and Justice Strong Institutions')

spi_mapper('un_sdg_map','SPI.D3.17.PTNS','Goal 17: Partnerships to achieve the Goal  ')

Data Sources

Cleaning for the Data Sources Indicators. Data Sources (4 Indicators):
- 4.1_SOCS - Indicator 4.1: censuses and surveys
- 4.2_SOAD - Indicator 4.2: administrative data
- 4.3_SOGS - Indicator 4.3: geospatial data
- 4.4_SOPC - Indicator 4.4: private/citizen generated data

Indicator 4.1: censuses and surveys

This indicator draws from data collected by the Statistical Performance Indicators team. The following censuses and surveys are considered:

Population & Housing census
Agriculture census
Business/establishment census
Household Survey on income/ consumption/ expenditure/ budget/ Integrated Survey
Agriculture survey
Labor Force Survey
Health/Demographic survey
Business/establishment survey

Scoring details

Population & Housing census

Population censuses collect data on the size, distribution and composition of population and information on a broad range of social and economic characteristics of the population. It also provides sampling frames for household and other surveys. Housing censuses provide information on the supply of housing units, the structural characteristics and facilities, and health and the development of normal family living conditions. Data obtained as part of the population census, including data on homeless persons, are often used in the presentation and analysis of the results of the housing census. It is recommended that population and housing censuses be conducted at least every 10 years.

1 Point. Population census done within last 10 years
0.5 Points. Population census done within last 20 years 0 Points. Otherwise

Agriculture census

Agriculture censuses collect information on agricultural activities, such as size of holding, land tenure, land use, employment and production, and provide basic structural data and sampling frames for agricultural surveys. Censuses of agriculture normally involves collecting key structural data by complete enumeration of all agricultural holdings, in combination with more detailed structural data using sampling methods. It is recommended that agricultural censuses be conducted at least every 10 years.

1 Point. census done within last 10 years
0.5 Points. census done within last 20 years
0 Points. Otherwise

Business/establishment census

Business/establishment censuses provide valuable information on all economic activities, number of employed and size of establishments in the economy. Business Register information is establishment-based and includes business location, organization type (e.g. subsidiary or parent), industry classification, and operating data (e.g., receipts and employment).

1 Point. census done within last 10 years
0.5 Points. census done within last 20 years
0 Points. Otherwise

Household Survey on income/consumption/expenditure/budget/Integrated Survey

These surveys collect data on household income (including income in kind), consumption and expenditure. They typically include income, expenditure, and consumption surveys, household budget surveys, integrated surveys. It is recommended that surveys on income and expenditure be conducted at least every 3 to 5 years.